'use strict';
const Service = require('egg').Service;

class HomeController extends Service {
  // 顶部四个标签
  async topdata (data) {
    const productnum = await this.app.mysql.count('products');
    const usernum = await this.app.mysql.count('users');
    const sql3 = 'select * from order1 where to_days(create_time) = to_days(now())';
    const daylist = await this.app.mysql.query(sql3);
    let daytotal = 0;
    console.log(daylist);
    for (let i = 0; i < daylist.length; i++) {
      daytotal = daytotal + parseInt(daylist[i].total_price);
    }
    const sql4 = "SELECT * FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')";
    const monthlist = await this.app.mysql.query(sql4);
    let monthtotal = 0;
    console.log(monthlist);
    for (let i = 0; i < monthlist.length; i++) {
      monthtotal = monthtotal + parseInt(monthlist[i].total_price);
    }
    return {
      code: 200,
      data: {
        productnum,
        usernum,
        daytotal,
        monthtotal,
      },
    };
  }
  // 近十天数据
  async getecharts (data) {
    const sql = 'SELECT a.click_date as date,IFNULL(b.total_price, 0) AS sum,b.create_time FROM( SELECT  date_sub(curdate(), interval 0 day) as click_date union all SELECT  date_sub(curdate(), interval 1 day) as click_date union all SELECT date_sub(curdate(), interval 2 day) as click_date union all SELECT date_sub(curdate(), interval 3 day) as click_date union all SELECT date_sub(curdate(), interval 4 day) as click_date union all SELECT date_sub(curdate(), interval 5 day) as click_date union all SELECT date_sub(curdate(), interval 6 day) as click_date union all SELECT date_sub(curdate(), interval 7 day) as click_date union all SELECT date_sub(curdate(), interval 8 day) as click_date union all SELECT date_sub(curdate(), interval 9 day) as click_date ) AS a left join( select * from  order1 WHERE datediff(NOW(), create_time) <= 10 ) AS b  on a.click_date = b.create_time ORDER BY a.click_date ASC;';
    const list = await this.app.mysql.query(sql);
    console.log('查询结果', list);
    let temptotal = 0;
    // const Xdate = [];
    const totalArr = [];
    console.log(list.length);
    for (let i = 0; i < list.length; i++) {
      if (i == list.length - 1) {
        console.log('最后一个', i);
        temptotal = temptotal + parseInt(list[i].sum);
        totalArr.push(temptotal);
      } else if (list[i].sum == 0) {
        console.log('第一种', i);
        totalArr.push(parseInt(list[i].sum));
      } else if (list[i].create_time && list[i + 1].create_time) {
        console.log('第二种', i);
        if (list[i].create_time.substring(0, 10) === list[i + 1].create_time.substring(0, 10)) {
          temptotal = temptotal + parseInt(list[i].sum);
        } else {
          temptotal = temptotal + parseInt(list[i].sum);
          totalArr.push(temptotal);
          // Xdate.push(list[i].date);
          temptotal = 0;
        }
      } else if (list[i].create_time && (list[i + 1].create_time == null || list[i + 1].create_time == undefined)) {
        console.log('第三种', i);
        temptotal = temptotal + parseInt(list[i].sum);
        totalArr.push(temptotal);
        // Xdate.push(list[i].date);
        temptotal = 0;
      }
    }
    return {
      code: 200,
      data: {
        // date: Xdate,
        total: totalArr,
        list,
      },
    };
  }

  // 四个图表
  async getfourecharts (data) {
    const usersql = 'SELECT `level`,count(*) num FROM users GROUP BY `level`;';
    const userlist = await this.app.mysql.query(usersql);
    const productsql = 'SELECT `product_type`,count(*) num FROM products GROUP BY `product_type`;';
    const productlist = await this.app.mysql.query(productsql);
    // const weeksql = "SELECT create_time,order_id FROM order1 WHERE YEARWEEK(date_format(create_time,'%Y%m%d'),1) = YEARWEEK(now(),1) ORDER BY create_time ASC";
    const weeksql = "select a.product_id,a.product_type,TIMESTAMPDIFF(DAY,DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY),b.create_time)+1 AS weeknumber,b.create_time from products as a right join (SELECT create_time,product_id FROM order1 WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1) ) as b on a.product_id = b.product_id ORDER BY b.create_time ASC";
    const weeklist = await this.app.mysql.query(weeksql);
    const week = [
      {
        week: 1,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 2,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 3,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 4,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 5,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 6,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
      {
        week: 7,
        sea: 0,
        line: 0,
        air: 0,
        railway: 0,
      },
    ];
    for (let i = 0; i < weeklist.length; i++) {
      fun(weeklist[i].weeknumber - 1, weeklist[i].product_type);
    }
    function fun (i, type) {
      if (type == 'sea') {
        week[i].sea++;
      } else if (type == 'line') {
        week[i].line++;
      } else if (type == 'air') {
        week[i].air++;
      } else if (type == 'railway') {
        week[i].railway++;
      }
    }
    const monthsql0 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')";
    const monthlist0 = await this.app.mysql.query(monthsql0);
    const monthsql1 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') - 1";
    const monthlist1 = await this.app.mysql.query(monthsql1);
    const monthsql2 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') - 2";
    const monthlist2 = await this.app.mysql.query(monthsql2);
    const monthsql3 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') - 3";
    const monthlist3 = await this.app.mysql.query(monthsql3);
    const monthsql4 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') - 4";
    const monthlist4 = await this.app.mysql.query(monthsql4);
    const monthsql5 = "SELECT sum(total_price) as sum FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') - 5";
    const monthlist5 = await this.app.mysql.query(monthsql5);
    const month = [monthlist5[0].sum, monthlist4[0].sum, monthlist3[0].sum, monthlist2[0].sum, monthlist1[0].sum, monthlist0[0].sum];
    for (let index = 0; index < month.length; index++) {
      if (month[index] == null) {
        month[index] = 0;
      }
    }
    return {
      code: 200,
      data: {
        userlist,
        productlist,
        week,
        month,
      },
    };
  }

  // 订单表最近数据
  async getordertable (data) {
    const sumsql = 'select a.product_type as sum_type,count(*) as sum from products as a right join order1 as b	on a.product_id = b.product_id GROUP BY a.product_type';
    const sumlist = await this.app.mysql.query(sumsql);
    const todaysql = 'select a.product_type as today_type,sum(b.total_price) as today from products as a right join (select * from order1 where to_days(create_time) = to_days(now())) as b on a.product_id = b.product_id GROUP BY a.product_type';
    const todaylist = await this.app.mysql.query(todaysql);
    const monthsql = "select a.product_type as month_type,sum(b.total_price) as month from products as a right join (SELECT * FROM order1 WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')) as b on a.product_id = b.product_id GROUP BY a.product_type";
    const monthlist = await this.app.mysql.query(monthsql);
    const totalsql = 'select a.product_type as total_type,sum(b.total_price) as total from products as a right join order1 as b on a.product_id = b.product_id GROUP BY a.product_type';
    const totallist = await this.app.mysql.query(totalsql);
    return {
      code: 200,
      data: {
        sumlist,
        todaylist,
        monthlist,
        totallist,
      },
    };
  }
}
module.exports = HomeController;
